Attribute VB_Name = "Weekly_Escalation_Report_AB"
' ==============================================================================
'  Auteur: Christian Klugesherz
'  Derniere Modification : 25/02/2014
' ==============================================================================

' ==============================================================================
' Option Declaration de variables
' ==============================================================================
Option Explicit
' When Option Explicit appears in a module, you must explicitly declare all variables
' using the Dim, Private, Public, ReDim, or Static statements.
' If you attempt to use an undeclared variable name, an error occurs at compile time.
' ==> Recommended to be used

' ==============================================================================
'                                 CONSTANTES
' ==============================================================================
Public Const Def_Col_Summary            As String = "J"
Public Const Def_Col_Escalation_Area    As String = "I"
Public Const Def_Col_First_Name         As String = "P"
Public Const Def_Col_Last_Name          As String = "O"
Public Const Def_Col_Escalation_Source  As String = "D"
Public Const Def_Col_Next_Action        As String = "M"
Public Const Def_Col_Status_Summary     As String = "K"
'

' ==============================================================================
'                                 FONCTIONS
' ==============================================================================

' ====================================
' Retourne semaine courante
' W1250
' ====================================
Public Function ESC_ReturnWeekNb_YM(Referdate As Date) As String
  Dim ISOWEEKNUMBER As Integer
  Dim d As Date
    ISOWEEKNUMBER = DatePart("ww", Referdate)
    If ISOWEEKNUMBER < 10 Then
        ESC_ReturnWeekNb_YM = "W" & _
                            VBA.Right(Year(Referdate), 2) & _
                            "0" & _
                            ISOWEEKNUMBER
    Else
        ESC_ReturnWeekNb_YM = "W" & _
                            VBA.Right(Year(Referdate), 2) & _
                            ISOWEEKNUMBER
    End If
End Function

'==============================================================================================
'                                      FONCTION PRINCIPALE
' ==============================================================================================
Sub Create_Weekly_Escalation_Report()
  Dim WeekNb As String
  Dim MailTo, MailCC, MailAttachement_Prefix_xlsx_FileName, MailAttachement_Directory_xlsx_FileName, MailAttach As String
  Dim out As String
  
  Dim srnbn As Integer
  Dim srnbo As Integer
  Dim srnb  As Integer
  Dim j, k   As Integer
  
  Dim tmp As String
  Dim tmp_ar() As String
  Dim tmp_arr() As String
  
  Dim Customer, Country, BP, SR As String
  Dim Escalation_Area, First_Name, Last_Name, Escalation_Source, Status_Summary, Next_Action As String
  
  Dim OL As Object
  Dim MyItem As Object
    
    WeekNb = ESC_ReturnWeekNb_YM(Now)
  
    ' Create an Outlook application.
    Set OL = CreateObject("Outlook.Application")
    Set MyItem = OL.CreateItem(0)
    
    MailTo = "BERNHART, ALAIN (ALAIN); BRUNEL, NICOLAS (NICOLAS); DE-JABRUN, GUILHEM (GUILHEM); DUPUY, FRANCK (FRANCK); Garceau, Martha P (Martha); Joyce, Rose Ann (Rose); Matthews, Charles C (Charles); NEGRI, JORGE MARCELO (JORGE MARCELO); SIMON, PASCAL (PASCAL); BABOT, VINCENT (VINCENT); BARBIER, NATHALIE (NATHALIE); BOHANNE, Philippe (Philippe)** CTR **; IVALDI, GUILLAUME (GUILLAUME); LING, RICHARD CL (RICHARD); OUSS, JEAN (JEAN); RINIE, HUBERT (HUBERT); ALLIVATO, KENNETH (KENNETH); BOUTROS, AMGAD (AMGAD); DUSSART, ANNE (ANNE); FAJERSSON, Gunnar (Gunnar); LEVY, MICHEL-JACQUES (MICHEL-JACQUES); RAKUSCHA, WOLFGANG (WOLFGANG); RAOUL, THIERRY (THIERRY); Schofield, Paul A (Paul); WAGNER, REMY (REMY); BONNIN, THIERRY (THIERRY); DESTOT, MATTHIEU (MATTHIEU)"
    MailCC = ""
        
    ' File name attached to the email
    MailAttachement_Prefix_xlsx_FileName = "Weekly escalation report Backlog  week "
    MailAttachement_Directory_xlsx_FileName = "C:\Documents and Settings\kluges1\My Documents\Technical Support\Escalation Manager\Work\"
    MailAttach = MailAttachement_Directory_xlsx_FileName & MailAttachement_Prefix_xlsx_FileName & DateDiff("w", DateSerial(Year(VBA.Date), 1, 1), VBA.Date) & "_" & VBA.Right(Year(Now), VBA.Len(Year(Now)) - 2) & ".xlsx"
            
    ' Creation of HTML
    out = "<html> <head> <title></title> </head> <body>"
    out = out & "Hello," & "<br>"
    out = out & "<br>"
    out = out & "Please find attached the Weekly Enterprise escalation report for Week :" & WeekNb & "<br>"
    out = out & "<br>"
    out = out & "<u> Reminder </u> <br>"
    out = out & "&nbsp;" & "&nbsp;" & "Call every Tuesday at 3 pm ((GMT+01:00) Brussels, Copenhagen, Madrid, Paris.) <br><br>"
    out = out & "&nbsp;" & "&nbsp;" & "<b>OpenTouch Conference Bridge:</b><br>"
    out = out & "&nbsp;" & "&nbsp;" & "France (Local): <b>+33 (0) 1767 041 54</b>  <i>(Additional dial-in numbers in the invitation)</i> <br>"
    out = out & "&nbsp;" & "&nbsp;" & "Participant Access Code: <b>0208115#</b>  <br>"
    out = out & "<br>"
    out = out & "<i> This is an abbreviated version.  For more information, see attachment file. </i>"
   
    srnbn = 0
    srnbo = 0
    srnb = 0
    For j = 4 To Cells(65536, 1).End(xlUp).Row
        If Not Rows(j).Hidden Then
            If Cells(j, Def_Col_Summary).Value <> Empty Then
                '----------------------
                ' Number of Escalation /
                ' NEW or OLD Escalaltion
                '----------------------
                If Cells(j, Def_Col_Summary).Font.Bold = True Then
                    If srnbn = 0 Then
                        out = out & "<br>" & "<br>"
                        out = out & "<u> <b>" & "ADDED THIS WEEK" & "</b> </u>" & "<br>" & "<br>"
                    End If
                    srnbn = srnbn + 1
                    srnb = srnbn
                Else
                    If srnbo = 0 Then
                        out = out & "<br>"
                        out = out & "<u> <b>" & "EXISTING ISSUES" & "</b> </u>" & "<br>" & "<br>"
                    End If
                    srnbo = srnbo + 1
                    srnb = srnbo
                End If

                ' Split the Cellule in: Customer, Country, BP, SR
                tmp = Cells(j, Def_Col_Summary).Value
                tmp_ar = Split(tmp, VBA.Chr(10))
                For k = 0 To UBound(tmp_ar)
                    If k = 0 Then
                        Customer = VBA.UCase(tmp_ar(k))
                    End If
                    If k = 1 Then
                        Country = VBA.UCase(tmp_ar(k))
                        tmp_arr = Split(Country, "COUNTRY:")
                        Country = tmp_arr(1)
                    End If
                    If k = 2 Then
                        BP = tmp_ar(k)
                    End If
                    If k = 3 Then
                        SR = tmp_ar(k)
                    End If
                Next k

                Escalation_Area = VBA.UCase(Cells(j, Def_Col_Escalation_Area).Value)
                First_Name = VBA.StrConv(Cells(j, Def_Col_First_Name).Value, vbProperCase)
                Last_Name = VBA.StrConv(Cells(j, Def_Col_Last_Name).Value, vbProperCase)
                Escalation_Source = VBA.UCase(Cells(j, Def_Col_Escalation_Source).Value)
                Status_Summary = Replace(Cells(j, Def_Col_Status_Summary).Value, VBA.Chr(10), " ")
                Next_Action = Replace(Cells(j, Def_Col_Next_Action).Value, VBA.Chr(10), "<br> ")
            
                out = out & "<b>" & srnb & ") " & Customer & "</b>" & "<br>"
                out = out & Escalation_Area & " - " & Country & " - " & BP & " - (" & First_Name & " " & Last_Name & ")" & "<br>"
                out = out & "Escalation Source: " & Escalation_Source & "<br>"
                If (srnbo = 0) Then
                    out = out & "<u>Status Summary</u>" & "<br>"
                    out = out & Status_Summary & "<br>"
                End If
                out = out & "<u>Next Action</u>" & "<br>"
                out = out & Next_Action & "<br>"
                out = out & "    " & "<br>"
            End If
        End If
     Next j
          
    out = out & "Best regards" & "<br>" & "<br>"
    out = out & "Alain Bernhart" & "<br>"
    out = out & "</body> </html>"
        
    With MyItem
        .To = MailTo
        .Cc = MailCC
        .Subject = "Weekly escalation report: " & WeekNb
        .HTMLBody = out
        .Categories = "Banking-Info"
    End With
    If ExisteFichier(MailAttach) Then
        MyItem.Attachments.Add (MailAttach)
    Else
        MsgBox "The file :" & VBA.Chr(13) & MailAttachement_Prefix_xlsx_FileName & DateDiff("w", DateSerial(Year(VBA.Date), 1, 1), VBA.Date) & "_" & VBA.Right(Year(Now), VBA.Len(Year(Now)) - 2) & ".xlsx" & VBA.Chr(13) & " doesn't exist in your directory" & VBA.Chr(13) & MailAttachement_Directory_xlsx_FileName
    End If
    MyItem.Display

End Sub

' ==============================================================================================
' ==============================================================================================

